<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page language="java" import="com.base.myproject.server.tools.*" %>
<%@ page language="java" import="com.base.myproject.client.jdbc.DataSet" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<%@page import="java.text.DecimalFormat"%><html>
<head>
<link href="report.css" rel="stylesheet" type="text/css"/>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>考勤统计</title>
<%
	String wherebyname = " ";
	String data = request.getParameter("data");
	String level = request.getParameter("level");
	String datas[] = data.split("_");
	String tree_depcode = datas[0];
	String ym = datas[1];
	if(datas.length == 3){
		String name = datas[2];
		if(name != null){
			wherebyname += " and a.bname like '%" + name + "%'";
		}
	}
	String sql = "select 年月,雇员编号,姓名,部门,出勤,例假,法定假,年假,补休,其他假,上月结转,调整,本月加班, " +
	"	补休*8+本月补休 as 本月补休,上月结转+调整+本月加班-(补休*8+本月补休) as 总欠假,convert(decimal(10,1),round((上月结转+调整+本月加班-(补休*8+本月补休))/8,1)) as 合天数 from(select CONVERT(varchar(7),a.ym,120) as 年月,a.icode,b.employno as 雇员编号,a.bname as 姓名, " +
	"	(select name from systeminfo where a.depcode=id) as 部门,(select count(bc) from v_schedule where bcode=a.bcode and bc!='' and bc not in(select batchno from hr_Attendance_WorkBatch where depcode='00') " +
	"	and CONVERT(varchar(7),ymd,120)='"+ym+"'  and depcode like '"+tree_depcode+"%') as 出勤,(select count(bc) from v_schedule where bc='DO' and bcode=a.bcode and CONVERT(varchar(7),ymd,120)='"+ym+"'  and depcode like '"+tree_depcode+"%') as 例假,(select count(bc) from v_schedule where bc='PH' and bcode=a.bcode " +
	"	and CONVERT(varchar(7),ymd,120)='"+ym+"'  and depcode like '"+tree_depcode+"%') as 法定假,(select count(bc) from v_schedule where bc='AL' and bcode=a.bcode and CONVERT(varchar(7),ymd,120)='"+ym+"'  and depcode like '"+tree_depcode+"%') as 年假,(select count(bc) from v_schedule where bc='CL' and bcode=a.bcode and CONVERT(varchar(7),ymd,120)='"+ym+"'  and depcode like '"+tree_depcode+"%') as 补休, " +
	"	(select count(bc) from v_schedule where bcode=a.bcode  and bc in (select batchno from hr_Attendance_WorkBatch where depcode='00' and batchno not in('CL','DO','PH','AL')) and CONVERT(varchar(7),ymd,120)='"+ym+"'  and depcode like '"+tree_depcode+"%') as 其他假,(select isnull(convert(decimal(10, 1),sum(period)),0)  from hr_Attendance_ot where  " +
	"	type='上月结转' and status='同意' and bcode=a.bcode and CONVERT(varchar(7),usedate,120)='"+ym+"'  and depcode like '"+tree_depcode+"%') as 上月结转, " +
	"	(select isnull(sum(period),0) from v_ot where type='调整' and bcode=a.bcode and CONVERT(varchar(7),usedate,120)='"+ym+"'  and depcode like '"+tree_depcode+"%') as 调整, " +
	"	(select isnull(sum(period),0)  from v_ot where type='加班' and bcode=a.bcode and CONVERT(varchar(7),usedate,120)='"+ym+"'  and depcode like '"+tree_depcode+"%') as 本月加班, " +
	"	(select isnull(sum(period),0)  from v_ot where type='补休' and bcode=a.bcode and CONVERT(varchar(7),usedate,120)='"+ym+"'  and depcode like '"+tree_depcode+"%') as 本月补休 " +
	"	from hr_Attendance_schedule a left join bcode_job b on a.bcode=b.bcode where CONVERT(varchar(7),ym,120)='"+ym+"'  and depcode like '"+tree_depcode+"%' "+wherebyname;
	
	if (!level.equals("")) sql += " and employlevel in("+level+")";
	sql += ") as att_total order by icode";
	DataSetTool DST = new DataSetTool();
	DataSet result = DST.getDataSet(sql);
	DataSet department = DST.getDataSet("select name from systeminfo where id='"+tree_depcode+"'");
%>
</head>
<body>
	<p align='center'><font size='4'><b>考勤统计表</b></font></p>
	查询年月：<%=ym %> 部门：<%=department.getValue(0,"name") %><br></br>
	<table style="border-collapse: collapse" cellspacing="0" cellpadding="0" border="1" width="100%" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 150%;border-color:#000000;"> 
		<tr bgcolor="#e8e8e0" align="center">
			<td>年月</td><td>姓名</td><td>员工编号</td><td>部门</td>
			<td>出勤</td><td>例休</td><td>法定假</td><td>年假</td>
			<td>补休</td><td>其他假</td><td>上月结转</td><td>调整</td>
			<td>本月加班</td><td>本月补休</td><td>总欠假</td><td>合天数</td>
		</tr>
		<%
			for(int i = 0;i < result.getRowCount();i++){
				
			%>
				<tr align="center">
					<td><%=ym %></td><td><%=result.getValue(i, "姓名") %></td><td><%=result.getValue(i, "雇员编号") %></td>
					<td><%=result.getValue(i, "部门")%></td><td><%=result.getValue(i, "出勤") %></td><td><%=result.getValue(i, "例假") %></td>
					<td><%=result.getValue(i, "法定假") %></td><td><%=result.getValue(i, "年假") %></td><td><%=result.getValue(i, "补休") %></td>
					<td><%=result.getValue(i, "其他假") %></td><td><%=result.getValue(i, "上月结转") %></td><td><%=result.getValue(i, "调整") %></td>
					<td><%=result.getValue(i, "本月加班") %></td><td><%=result.getValue(i, "本月补休") %></td>
					<td><%=result.getValue(i, "总欠假") %></td><td><%=result.getValue(i, "合天数") %></td>
				</tr>
			<%
			}
		%>
	</table>
	<br></br>
	<p></p>
		<p align="center">考勤员:___________________ 部门经理：___________________ 人力资源部:___________________</p>
		
	
</body>
</html>